import win32com.client
import urllib, urllib2

#xlsApp = win32com.client.Dispatch('Excel.Application')
#xlsBook = xlsApp.Workbooks.Open('d:\\1.xls')
#xlsSheet = xlsBook.Sheet[0]

class easyExcel(object):
    """A utility to make it easier to get at Excel.  Remembering
    to save the data is your problem, as is  error handling.
    Operates on one workbook at a time."""

    def __init__(self, filename=None):
        self.xlApp = win32com.client.Dispatch('Excel.Application')
        if filename:
            self.filename = filename
            self.xlBook = self.xlApp.Workbooks.Open(filename)
        else:
            self.xlBook = self.xlApp.Workbooks.Add()
            self.filename = ''  
    
    def save(self, newfilename=None):
        if newfilename:
            self.filename = newfilename
            self.xlBook.SaveAs(newfilename)
        else:
            self.xlBook.Save()    

    def close(self):
        self.xlBook.Close(SaveChanges=0)
        del self.xlApp
        
    def getCell(self, sheet, row, col):
        "Get value of one cell"
        sht = self.xlBook.Worksheets(sheet)
        return sht.Cells(row, col).Value
    
    def setCell(self, sheet, row, col, value):
        "set value of one cell"
        sht = self.xlBook.Worksheets(sheet)
        sht.Cells(row, col).Value = value
    
    def getRange(self, sheet, row1, col1, row2, col2):
        "return a 2d array (i.e. tuple of tuples)"
        sht = self.xlBook.Worksheets(sheet)
        return sht.Range(sht.Cells(row1, col1), sht.Cells(row2, col2)).Value
        
    def setRange(self, sheet, leftCol, topRow, data):
        """insert a 2d array starting at given location. 
        Works out the size needed for itself"""
    
        bottomRow = topRow + len(data) - 1
        rightCol = leftCol + len(data[0]) - 1
        sht = self.xlBook.Worksheets(sheet)
        sht.Range(
            sht.Cells(topRow, leftCol), 
            sht.Cells(bottomRow, rightCol)
            ).Value = data
        
    def getContiguousRange(self, sheet, row, col):
        """Tracks down and across from top left cell until it
        encounters blank cells; returns the non-blank range.
        Looks at first row and column; blanks at bottom or right
        are OK and return None witin the array"""
    
        sht = self.xlBook.Worksheets(sheet)
    
        # find the bottom row
        bottom = row
        while sht.Cells(bottom + 1, col).Value not in [None, '']:
            bottom = bottom + 1
    
        # right column
        right = col
        while sht.Cells(row, right + 1).Value not in [None, '']:
            right = right + 1
    
        return sht.Range(sht.Cells(row, col), sht.Cells(bottom, right)).Value

    def fixStringsAndDates(self, aMatrix):
        # converts all unicode strings and times
        newmatrix = []
        for row in aMatrix:
            newrow = []
            for cell in row:
                if type(cell) is UnicodeType:
                    newrow.append(str(cell))
                elif type(cell) is TimeType:
                    newrow.append(int(cell))
                else:
                    newrow.append(cell)
            newmatrix.append(tuple(newrow))
        return newmatrix

def getContact():
    spr = easyExcel('d:\\1.xls')
    contacts = []
    
    for row in range(2,44):#44
        contact = []
        name = spr.getCell('20081023', row, 3)
        if name is not None:
            print row,'-----------------'
            name = name.replace(' ','')
            name = name.replace(unichr(int('3000', 16)), u'')
            contact.append(name)            
            print name
            mail = spr.getCell('20081023', row, 8)
            if mail is not None and mail != '':
                contact.append(mail)
                print mail
                username = mail.split('@')[0]
                print username
                contact.append(username)
            contacts.append(contact)
        
        contact = []
        name = spr.getCell('20081023', row, 10)
        if name is not None:
            print row,'-----------------'
            name = name.replace(' ','')
            name = name.replace(unichr(int('3000', 16)), u'')
            contact.append(name)
            print name
            mail = spr.getCell('20081023', row, 15)
            if mail is not None and mail != '':
                contact.append(mail)
                print mail
                username = mail.split('@')[0]
                print username
                contact.append(username)
            contacts.append(contact)    
        
    spr.close()    
    print contacts
    return contacts

def encoding(s):
    cl = ['utf8', 'gb2312', 'gbk']
    for a in cl:
        try:
            s.decode(a)
            return a
        except UnicodeEncodeError:
            pass
    return 'unknown'

def signup(contact):
    values = {'username':contact[2], 'name':contact[0].encode('utf8'), \
              'password1':'111111', 'password2':'111111', \
              'email':contact[1]}
    data = urllib.urlencode(values)
    req = urllib2.Request('http://192.168.43.127:81/signup/', data)
    response = urllib2.urlopen(req)
    #result = response.read()
    #if response.getcode() != 200:
    #    print 'Error in signup'
    #f = open('error.html', 'w')
    #f.write(result)
    #f.close()
        
    print 'signup ok for %s'%contact[0]

if __name__ == '__main__':
    contacts = getContact()
    for c in contacts:
        signup(c)
        
    print 'sign up finished!'